Create a calculated field to determine sale amount
The report must show the total sale amount, which is not a field in the database, so it must be calculated. The total sale amount is equal to the product quantity multiplied by the unit cost, which are fields in the database. A calculated field must be created and added as an entry underneath the Visible Fields tab.
Within the Visible Fields tab:
- Click the empty Table field to the right of the "Products" table entry.
- Click the down arrow that appears in the field to get a list of available tables.
- Select '<calculated>' from the drop-down.
- Click inside the Field field (below the Table field), then click the ellipses icon that appears inside the field to launch the SQL Editor. Enter the following into the text field:
= Order_Details.quantity * Products.unit_price - Click OK to return to the Build Query.
- Click in the Type field, then click the down arrow within the field to get a drop-down with a list of data types.
- Select Float from the drop-down.
- Click in the As field and enter 'sale_amount'. This new calculated field can now be used in the same fashion as other fields in the database.
- Click OK, then Finish to continue.
You can now see the new multi-column list box object shown within the DataBlock Designer with all of the fields added.